﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Xml;
using PropertyExpression.Common;

namespace PropertyExpression.ControllableQuery.Toolkit
{
    public static class QueryChecker
    {
        public static void CheckNonQueries(string connectionString, params Func<CheckQueryContext, INonQuery>[] queryFuncs)
        {
            foreach (var queryFunc in queryFuncs)
            {
                string queryText;
                IQuery query;
                ExtractSqlParameter(connectionString, queryFunc, out queryText, out query);
            }
        }

        public static void CheckQueries<T>(string connectionString, params Func<CheckQueryContext, IQuery<T>>[] queryFuncs)
        {
            foreach (var queryFunc in queryFuncs)
            {
                string queryText;
                IQuery query;
                var sqlParamInfoList = ExtractSqlParameter(connectionString, queryFunc, out queryText, out query);

                var dataInfoType = typeof(T);
                var dataInfoPropertyInfos = dataInfoType.GetProperties();
                var dataInfoPropertyInfoDictionary = dataInfoPropertyInfos.ToDictionary(
                    propertyInfo => propertyInfo.Name);
                var schemaTable = WithSqlConnection(
                    connectionString,
                    connection => WithSqlCommand(
                        connection,
                        sqlCommand =>
                            {
                                sqlCommand.CommandText = GetCommandText(
                                    sqlParamInfoList.Select(
                                        sqlParamInfo => Tuple.Create(sqlParamInfo.ParamInfo.Name, sqlParamInfo.SqlType)
                                        ),
                                    queryText);

                                using (var sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SchemaOnly))
                                {
                                    return sqlDataReader.GetSchemaTable();
                                }
                            })
                    );
                foreach (DataRow dataRow in schemaTable.Rows)
                {
                    var closuredDataRow = dataRow;
                    dataInfoPropertyInfoDictionary.GetOptionalValue(GetColumnName(closuredDataRow)).Process(
                        value =>
                            {
                                if (!TypesAreCompatible(value.PropertyType, GetDataType(closuredDataRow),
                                                        GetAllowDbNull(closuredDataRow)))
                                {
                                    throw new QueryCheckException(
                                        string.Format(
                                            @"The type of query result property '{0}' was incorrect. Expected DataType = '{1}', AllowDBNull = '{2}', but was type '{3}'.
QueryType = '{4}'",
                                            GetColumnName(closuredDataRow),
                                            GetDataType(closuredDataRow),
                                            GetAllowDbNull(closuredDataRow),
                                            value.PropertyType,
                                            GetQueryType(query)));
                                }
                                Console.WriteLine(@"{0} {1} '{2}' {3}",
                                                  GetColumnName(closuredDataRow),
                                                  GetDataType(closuredDataRow),
                                                  GetAllowDbNull(closuredDataRow),
                                                  value.PropertyType);
                            },
                        () =>
                            {
                                throw new QueryCheckException(
                                    string.Format(
                                        @"The query result type does not contain a definition for property '{0}'.
QueryType = '{1}'",
                                        GetColumnName(closuredDataRow),
                                        GetQueryType(query)));
                            }
                        );
                }
                var schemaTableDictionary = schemaTable.Rows.Cast<DataRow>().ToDictionary(GetColumnName);
                foreach (var propertyInfo in dataInfoPropertyInfos)
                {
                    if (!schemaTableDictionary.GetOptionalValue(propertyInfo.Name).HasValue())
                    {
                        throw new QueryCheckException(
                            string.Format(
                                @"The query result does not contain a column '{0}'.
QueryType = '{1}'",
                                propertyInfo.Name,
                                GetQueryType(query)));
                    }
                }
            }
        }

        private static IEnumerable<SqlParamInfo> ExtractSqlParameter(
            string connectionString, Func<CheckQueryContext, IQuery> queryFunc,
            out string queryText, out IQuery query)
        {
            var paramInfoList = new List<ParamInfo>();
            query = queryFunc(new CheckQueryContext(paramInfoList));
            queryText = query.TransformText();
            var innerQuery = query;
            var innerQueryText = queryText;
            var executionResult = WithSqlConnection(
                connectionString,
                connection =>
                    {
                        WithSqlCommand(
                            connection,
                            sqlCommand =>
                                {
                                    sqlCommand.CommandText = "SET SHOWPLAN_XML ON";
                                    sqlCommand.ExecuteNonQuery();
                                    return new {};
                                }
                            );

                        var dictionary = new Dictionary<string, string>();
                        var result =
                            new
                                {
                                    ExecuteQueryResult = ExecuteQuery(connection, innerQueryText, paramInfoList, dictionary, innerQuery),
                                    dictionary
                                };

                        WithSqlCommand(
                            connection,
                            sqlCommand =>
                                {
                                    sqlCommand.CommandText = "SET SHOWPLAN_XML OFF";
                                    sqlCommand.ExecuteNonQuery();
                                    return new {};
                                }
                            );

                        return result;
                    }
                );
            var xmlDocument = new XmlDocument();
            xmlDocument.LoadXml((string) executionResult.ExecuteQueryResult);
            var xmlNamespaceManager = new XmlNamespaceManager(xmlDocument.NameTable);
            xmlNamespaceManager.AddNamespace("a", "http://schemas.microsoft.com/sqlserver/2004/07/showplan");
            Console.WriteLine(queryText);
            var sqlParamInfoList = paramInfoList.Select(
                paramInfo =>
                new SqlParamInfo(paramInfo, executionResult.dictionary.GetOptionalValue(paramInfo.Name).Process(
                    value => value,
                    () => paramInfo.SqlTypeFunc(paramInfo, xmlDocument, xmlNamespaceManager)))
                ).ToList();
            foreach (var sqlParamInfo in sqlParamInfoList)
            {
                sqlParamInfo.ParamInfo.CheckParameterTypeAction(sqlParamInfo, queryText, GetQueryType(query));
                Console.WriteLine("{0} {1} {2}",
                                  sqlParamInfo.ParamInfo.Name,
                                  sqlParamInfo.ParamInfo.ParamType,
                                  sqlParamInfo.SqlType);
            }
            return sqlParamInfoList;
        }

        private static Type GetQueryType(IQuery query)
        {
            return query.GetType();
        }

        private static object ExecuteQuery(
            SqlConnection connection, string queryText, IEnumerable<ParamInfo> paramInfoList, Dictionary<string, string> dictionary, IQuery query)
        {
            var commandText = GetCommandText(
                paramInfoList.Select(
                    paramInfo => Tuple.Create(
                        paramInfo.Name,
                        paramInfo.SqlDeclareInfoFunc(paramInfo).Process(
                            value => value,
                            () => dictionary
                                      .GetOptionalValue(paramInfo.Name).Process(value => value, () => "VARBINARY(MAX)")
                            ))
                    ),
                queryText);

            return WithSqlCommand<Func<object>>(
                connection,
                sqlCommand =>
                    {
                        sqlCommand.CommandText = commandText;
                        try
                        {
                            var executeScalar = sqlCommand.ExecuteScalar();
                            return () => executeScalar;
                        }
                        catch (SqlException sqlException)
                        {
                            if (sqlException.Number == 1060)
                            {
                                dictionary.Add(InternalUtil.ToLines(commandText).ToArray()[sqlException.LineNumber - 1], "bigint");
                                return () => ExecuteQuery(connection, queryText, paramInfoList, dictionary, query);
                            }
                            else
                            {
                                throw new QueryCheckException(sqlCommand.CommandText, sqlException, GetQueryType(query));
                            }
                        }
                    }
                )();
        }

        private static string GetCommandText(IEnumerable<Tuple<string, string>> paramInfoList, string queryText)
        {
            var paramStringBuilder = new StringBuilder();
            foreach (var paramInfo in paramInfoList)
            {
                paramStringBuilder.AppendFormat("DECLARE {0} {1}", paramInfo.Item1, paramInfo.Item2);
                paramStringBuilder.AppendLine();
            }
            return string.Format(@"{0}
{1}",
                                 paramStringBuilder,
                                 queryText);
        }

        private static TResult WithSqlConnection<TResult>(string connectionString, Func<SqlConnection, TResult> func)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                return func(connection);
            }
        }

        private static TResult WithSqlCommand<TResult>(SqlConnection connection, Func<SqlCommand, TResult> func)
        {
            using (var sqlCommand = new SqlCommand())
            {
                sqlCommand.Connection = connection;

                return func(sqlCommand);
            }
        }

        private static bool TypesAreCompatible(Type dataInfoPropertyType, Type dbType, bool allowDbNull)
        {
            if (dataInfoPropertyType.IsClass)
            {
                return dataInfoPropertyType.Equals(dbType);
            }
            else
            {
                if (allowDbNull)
                {
                    return NullableGenericArgumentIsEqualToType(dataInfoPropertyType, dbType);
                }
                else
                {
                    if (dataInfoPropertyType.Equals(dbType))
                    {
                        return true;
                    }
                    else
                    {
                        return NullableGenericArgumentIsEqualToType(dataInfoPropertyType, dbType);
                    }
                }                
            }
        }

        private static bool NullableGenericArgumentIsEqualToType(Type dataInfoPropertyType, Type dbType)
        {
            if (dataInfoPropertyType.IsGenericType &&
                dataInfoPropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                return dataInfoPropertyType.GetGenericArguments().Single().Equals(dbType);
            }
            else
            {
                return false;
            }
        }

        private static bool GetAllowDbNull(DataRow closuredDataRow)
        {
            return (bool) closuredDataRow["AllowDBNull"];
        }

        private static Type GetDataType(DataRow dataRow)
        {
            return (Type) dataRow["DataType"];
        }

        private static string GetColumnName(DataRow dataRow)
        {
            return (string) dataRow["ColumnName"];
        }
    }
}